"""
文件数据CRUD操作
包含文件记录的增删改查和相关业务逻辑
"""

import os
from typing import List, Optional, Tuple

from app.models.file_models import File, FileInfo, FileFilter, FileStatistics
from app.schemas.file_schemas import FileUpdateRequest
from common.database import DatabaseService


# ---------- 基础CRUD操作 ----------


async def save_file_record(
		db: DatabaseService,
		file_name: str,
		file_ext: Optional[str] = None,
		file_size: Optional[int] = None,
		mime_type: Optional[str] = None,
		store_path: Optional[str] = None,
		url: Optional[str] = None,
		md5: Optional[str] = None,
		biz_type: Optional[str] = None,
		biz_id: Optional[int] = None,
		creator_id: Optional[int] = None,
) -> int:
	"""
	保存文件记录到数据库

	Args:
			db: 数据库服务
			file_name: 原始文件名
			file_ext: 文件扩展名
			file_size: 文件大小（字节）
			mime_type: MIME类型
			store_path: 存储路径
			url: 访问URL
			md5: 文件MD5值
			biz_type: 业务类型
			biz_id: 业务ID
			creator_id: 上传用户ID

	Returns:
			int: 新创建的文件记录ID
	"""
	sql = """
          INSERT INTO base_file (biz_type, biz_id, file_name, file_ext, file_size, mime_type,
                                 store_path, url, md5, status, creator_id)
          VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) \
          """

	return db.insert(
		sql,
		(
			biz_type,
			biz_id,
			file_name,
			file_ext,
			file_size,
			mime_type,
			store_path,
			url,
			md5,
			1,
			creator_id,
		),
	)


async def save_file_record_legacy(
		db: DatabaseService,
		file_name: str,
		file_ext: Optional[str] = None,
		file_size: Optional[int] = None,
		mime_type: Optional[str] = None,
		store_path: Optional[str] = None,
		url: Optional[str] = None,
		md5: Optional[str] = None,
		biz_type: Optional[str] = None,
		biz_id: Optional[str] = None,
		creator_id: Optional[int] = None,
) -> int:
	"""
	兼容原有接口的文件记录保存函数
	"""
	return await save_file_record(
		db,
		file_name,
		file_ext,
		file_size,
		mime_type,
		store_path,
		url,
		md5,
		biz_type,
		biz_id,
		creator_id,
	)


async def save_file_record_with_db(
		db: DatabaseService,
		file_name: str,
		file_ext: Optional[str] = None,
		file_size: Optional[int] = None,
		mime_type: Optional[str] = None,
		store_path: Optional[str] = None,
		url: Optional[str] = None,
		md5: Optional[str] = None,
		biz_type: Optional[str] = None,
		biz_id: Optional[str] = None,
		creator_id: Optional[int] = None,
) -> int:
	"""
	使用指定数据库连接保存文件记录
	"""
	sql = """
          INSERT INTO base_file (biz_type, biz_id, file_name, file_ext, file_size, mime_type,
                                 store_path, url, md5, status, creator_id)
          VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) \
          """

	return db.insert(
		sql,
		(
			biz_type,
			biz_id,
			file_name,
			file_ext,
			file_size,
			mime_type,
			store_path,
			url,
			md5,
			1,
			creator_id,
		),
	)


async def get_file_record(db: DatabaseService, file_id_or_key: str) -> Optional[File]:
	"""
	根据文件ID或唯一标识获取文件记录

	Args:
			db: 数据库服务
			file_id_or_key: 文件ID或文件唯一标识（如URL中的key）

	Returns:
			File: 文件记录对象，不存在则返回None
	"""
	return await get_file_record_with_db(db, file_id_or_key)


async def get_file_record_with_db(
		db: DatabaseService, file_id_or_key: str
) -> Optional[File]:
	"""
	使用指定数据库连接获取文件记录
	"""
	# 尝试按ID查询
	if file_id_or_key.isdigit():
		sql = """
              SELECT id,
                     biz_type,
                     biz_id,
                     file_name,
                     file_ext,
                     file_size,
                     mime_type,
                     store_path,
                     url,
                     md5,
                     status,
                     creator_id,
                     create_time,
                     update_time
              FROM base_file
              WHERE id = %s
                AND status = 1 \
              """
		row = db.get_one(sql, (int(file_id_or_key),))
	else:
		# 按URL中的key查询（提取文件名部分）
		sql = """
              SELECT id,
                     biz_type,
                     biz_id,
                     file_name,
                     file_ext,
                     file_size,
                     mime_type,
                     store_path,
                     url,
                     md5,
                     status,
                     creator_id,
                     create_time,
                     update_time
              FROM base_file
              WHERE url LIKE %s
                AND status = 1 \
              """
		row = db.get_one(sql, (f"%{file_id_or_key}%",))

	return File(**row) if row else None


async def get_file_by_id(db: DatabaseService, file_id: int) -> Optional[File]:
	"""
	根据文件ID获取文件记录

	Args:
			db: 数据库服务
			file_id: 文件ID

	Returns:
			File: 文件记录对象，不存在则返回None
	"""
	sql = """
          SELECT id,
                 biz_type,
                 biz_id,
                 file_name,
                 file_ext,
                 file_size,
                 mime_type,
                 store_path,
                 url,
                 md5,
                 status,
                 creator_id,
                 create_time,
                 update_time
          FROM base_file
          WHERE id = %s \
          """
	row = db.get_one(sql, (file_id,))
	return File(**row) if row else None


async def get_files_by_filter(
		db: DatabaseService, filter_params: FileFilter, page: int = 1, page_size: int = 20
) -> Tuple[int, List[FileInfo]]:
	"""
	根据过滤条件分页查询文件列表

	Args:
			db: 数据库服务
			filter_params: 过滤条件
			page: 页码
			page_size: 每页大小

	Returns:
			Tuple[int, List[FileInfo]]: (总数, 文件信息列表)
	"""
	# 构建WHERE条件
	where_conditions = ["status = 1"]  # 只查询有效文件
	params = []

	if filter_params.biz_type:
		where_conditions.append("biz_type = %s")
		params.append(filter_params.biz_type)

	if filter_params.biz_id:
		where_conditions.append("biz_id = %s")
		params.append(filter_params.biz_id)

	if filter_params.creator_id:
		where_conditions.append("creator_id = %s")
		params.append(filter_params.creator_id)

	if filter_params.file_ext:
		where_conditions.append("file_ext = %s")
		params.append(filter_params.file_ext)

	if filter_params.mime_type:
		where_conditions.append("mime_type = %s")
		params.append(filter_params.mime_type)

	if filter_params.status is not None:
		where_conditions[-1] = "status = %s"  # 替换默认的status条件
		params.append(filter_params.status)

	if filter_params.keyword:
		where_conditions.append("file_name LIKE %s")
		params.append(f"%{filter_params.keyword}%")

	where_clause = " AND ".join(where_conditions)

	# 查询总数
	count_sql = f"SELECT COUNT(*) as total FROM base_file WHERE {where_clause}"
	count_row = db.get_one(count_sql, tuple(params))
	total = count_row["total"] if count_row else 0

	# 查询数据
	offset = (page - 1) * page_size
	data_sql = f"""
        SELECT id, biz_type, biz_id, file_name, file_ext, file_size, mime_type,
               url, creator_id, create_time, update_time
        FROM base_file
        WHERE {where_clause}
        ORDER BY create_time DESC
        LIMIT %s OFFSET %s
    """
	params.extend([page_size, offset])

	rows = db.get_all(data_sql, tuple(params))
	files = [FileInfo(**row) for row in rows]

	return total, files


async def update_file_record(
		db: DatabaseService, file_id: int, update_data: FileUpdateRequest
) -> Optional[File]:
	"""
	更新文件记录

	Args:
			db: 数据库服务
			file_id: 文件ID
			update_data: 更新数据

	Returns:
			File: 更新后的文件记录，不存在则返回None
	"""
	# 检查文件是否存在
	existing = await get_file_by_id(db, file_id)
	if not existing:
		return None

	# 构建更新字段
	update_fields = []
	update_values = []

	# 总是处理biz_type和biz_id字段，即使它们是None（用于解绑操作）
	update_fields.append("biz_type = %s")
	update_values.append(update_data.biz_type)

	update_fields.append("biz_id = %s")
	update_values.append(update_data.biz_id)

	if update_data.status is not None:
		update_fields.append("status = %s")
		update_values.append(update_data.status)

	# 执行更新
	update_sql = f"UPDATE base_file SET {', '.join(update_fields)} WHERE id = %s"
	update_values.append(file_id)

	affected_rows = db.update(update_sql, tuple(update_values))
	if affected_rows == 0:
		return None

	# 返回更新后的数据
	return await get_file_by_id(db, file_id)


async def delete_file_record(
		db: DatabaseService, file_id: int, force_delete: bool = False
) -> bool:
	"""
	删除文件记录

	Args:
			db: 数据库服务
			file_id: 文件ID
			force_delete: 是否物理删除文件

	Returns:
			bool: 删除成功返回True，文件不存在返回False
	"""
	# 获取文件信息
	file_record = await get_file_by_id(db, file_id)
	if not file_record:
		return False

	if force_delete:
		# 物理删除文件
		if file_record.store_path and os.path.exists(file_record.store_path):
			try:
				os.remove(file_record.store_path)
			except OSError:
				pass  # 文件删除失败，但仍然删除数据库记录

		# 删除数据库记录
		sql = "DELETE FROM base_file WHERE id = %s"
	else:
		# 逻辑删除（标记为无效）
		sql = "UPDATE base_file SET status = 0 WHERE id = %s"

	affected_rows = db.update(sql, (file_id,))
	return affected_rows > 0


async def batch_delete_files(
		db: DatabaseService, file_ids: List[int], force_delete: bool = False
) -> Tuple[int, List[int]]:
	"""
	批量删除文件

	Args:
			db: 数据库服务
			file_ids: 文件ID列表
			force_delete: 是否物理删除文件

	Returns:
			Tuple[int, List[int]]: (成功删除数量, 删除失败的文件ID列表)
	"""
	success_count = 0
	failed_ids = []

	for file_id in file_ids:
		try:
			if await delete_file_record(db, file_id, force_delete):
				success_count += 1
			else:
				failed_ids.append(file_id)
		except Exception:
			failed_ids.append(file_id)

	return success_count, failed_ids


async def get_file_statistics(
		db: DatabaseService, filter_params: Optional[FileFilter] = None
) -> FileStatistics:
	"""
	获取文件统计信息

	Args:
			db: 数据库服务
			filter_params: 过滤条件（可选）

	Returns:
			FileStatistics: 文件统计信息
	"""
	# 构建WHERE条件
	where_conditions = ["status = 1"]
	params = []

	if filter_params:
		if filter_params.biz_type:
			where_conditions.append("biz_type = %s")
			params.append(filter_params.biz_type)

		if filter_params.creator_id:
			where_conditions.append("creator_id = %s")
			params.append(filter_params.creator_id)

	where_clause = " AND ".join(where_conditions)

	# 基础统计
	basic_sql = f"""
        SELECT
            COUNT(*) as total_count,
            COALESCE(SUM(file_size), 0) as total_size
        FROM base_file
        WHERE {where_clause}
    """
	basic_row = db.get_one(basic_sql, tuple(params))

	# 按类型统计
	type_sql = f"""
        SELECT
            CASE
                WHEN mime_type LIKE 'image/%' THEN 'image'
                WHEN mime_type IN (
                    'application/pdf', 'application/msword',
                    'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
                    'application/vnd.ms-excel',
                    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
                    'text/plain', 'text/csv'
                ) THEN 'document'
                ELSE 'other'
            END as file_type,
            COUNT(*) as count
        FROM base_file
        WHERE {where_clause}
        GROUP BY file_type
    """
	type_rows = db.get_all(type_sql, tuple(params))

	# 整理统计结果
	type_counts = {row["file_type"]: row["count"] for row in type_rows}

	return FileStatistics(
		total_count=basic_row["total_count"] if basic_row else 0,
		total_size=basic_row["total_size"] if basic_row else 0,
		image_count=type_counts.get("image", 0),
		document_count=type_counts.get("document", 0),
		other_count=type_counts.get("other", 0),
	)


async def get_files_by_business(
		db: DatabaseService, biz_type: str, biz_id: str
) -> List[FileInfo]:
	"""
	根据业务类型和业务ID获取文件列表

	Args:
			db: 数据库服务
			biz_type: 业务类型
			biz_id: 业务ID

	Returns:
			List[FileInfo]: 文件信息列表
	"""
	sql = """
          SELECT id,
                 biz_type,
                 biz_id,
                 file_name,
                 file_ext,
                 file_size,
                 mime_type,
                 url,
                 creator_id,
                 create_time,
                 update_time
          FROM base_file
          WHERE biz_type = %s
            AND biz_id = %s
            AND status = 1
          ORDER BY create_time DESC \
          """
	rows = db.get_all(sql, (biz_type, biz_id))
	return [FileInfo(**row) for row in rows]


async def check_file_exists_by_md5(db: DatabaseService, md5: str) -> Optional[File]:
	"""
	根据MD5值检查文件是否已存在

	Args:
			db: 数据库服务
			md5: 文件MD5值

	Returns:
			File: 已存在的文件记录，不存在则返回None
	"""
	sql = """
          SELECT id,
                 biz_type,
                 biz_id,
                 file_name,
                 file_ext,
                 file_size,
                 mime_type,
                 store_path,
                 url,
                 md5,
                 status,
                 creator_id,
                 create_time,
                 update_time
          FROM base_file
          WHERE md5 = %s
            AND status = 1
          ORDER BY create_time DESC LIMIT 1 \
          """
	row = db.get_one(sql, (md5,))
	return File(**row) if row else None


async def cleanup_orphaned_files(db: DatabaseService) -> int:
	"""
	清理孤儿文件（数据库中不存在对应记录的物理文件）

	Args:
			db: 数据库服务

	Returns:
			int: 清理的文件数量
	"""
	# 这里实现清理逻辑，需要根据实际存储方式调整
	# 例如：扫描存储目录，检查文件是否在数据库中有对应记录
	cleanup_count = 0

	# TODO: 实现具体的清理逻辑
	# 1. 扫描存储目录
	# 2. 检查每个文件是否在数据库中有记录
	# 3. 删除没有记录的文件

	return cleanup_count


async def check_file_unbound(db: DatabaseService, file_id: int) -> bool:
	"""
	检查文件是否存在且未被绑定（biz_type和biz_id均为NULL或空字符串）

	Args:
			db: 数据库服务
			file_id: 文件ID

	Returns:
			bool: 文件存在且未被绑定返回True，否则返回False
	"""
	sql = """
          SELECT id 
          FROM base_file 
          WHERE id = %s 
          AND (biz_id IS NULL OR LENGTH(biz_id) = 0)
          AND (biz_type IS NULL OR LENGTH(biz_type) = 0)
          """
	result = db.get_one(sql, (file_id,))
	return result is not None
